﻿using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using NET6.WebAPI.Dtos.Tool;
using NET6.WebAPI.Entities.Base;
using NET6.WebAPI.Entities.User;
using NET6.WebAPI.Enums;
using NET6.WebAPI.Extensions;
using NET6.WebAPI.Reqs.Base;
using NET6.WebAPI.Reqs.Tool;
using NET6.WebAPI.Reqs.User;
using static NET6.WebAPI.Entities.Base.BusinessBase;

namespace NET6.WebAPI.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class UserOfEntityFrameworkController : ControllerBase
    {
        private readonly ProjectDbContext _db;
        private readonly IMapper _mapper;

        public UserOfEntityFrameworkController(ProjectDbContext dbContext, IMapper mapper)
        {
            _db = dbContext;
            _mapper = mapper;
        }

        [HttpPost]
        public async Task<IActionResult> List(PagingReq<Sys_UserQueryReq> req)
        {
            var query = _db.Sys_User
                .AsNoTracking() // 只查询的情况下可以取消自动跟踪实体, 能提升效率
                .Where(it => it.RecordStatus != RecordStatusEnum.Deleted);

            if (req.Body.UserName.HasValue())
            {
                query = query.Where(it => it.UserName.Contains(req.Body.UserName.Trim()));
            }

            var pageData = await query
                .OrderBy(it => it.RecordStatus)
                .ThenByDescending(it => it.CreateTime)
                .GetPageListAsync(req.PageNumber, req.PageSize);

            return Ok(new HttpResponseDto(HttpResponseCode.QuerySuccess, pageData));
        }

        [HttpPost]
        public async Task<IActionResult> JoinList(PagingReq<Sys_UserQueryReq> req)
        {
            var query = from u in _db.Sys_User
                        join l in _db.Sys_UserLog on u.ID equals l.UserID
                        where u.RecordStatus != RecordStatusEnum.Deleted
                        orderby u.RecordStatus, u.CreateTime descending
                        select new
                        {
                            u.ID,
                            u.UserName,
                            u.Password,
                            u.RecordStatus,
                            u.CreateTime,
                            l.Info
                        };

            query = query.AsNoTracking();

            if (req.Body.UserName.HasValue())
            {
                query = query.Where(it => it.UserName.Contains(req.Body.UserName.Trim()));
            }

            var pageData = await query.GetPageListAsync(req.PageNumber, req.PageSize);

            return Ok(new HttpResponseDto(HttpResponseCode.QuerySuccess, pageData));
        }

        [HttpPost]
        public IActionResult JoinListOfSql(PagingReq<Sys_UserQueryReq> req)
        {
            string where = string.Empty;
            var parms = new List<SqlParameter>();

            if (req.Body.UserName.HasValue()) 
            {
                where += $"and UserName like @UserName ";
                parms.Add(new SqlParameter("@UserName", $"%{req.Body.UserName.Trim()}%"));
            }

            string sql = $@"
                select u.*, l.Info from Sys_User u 
                inner join Sys_UserLog l on l.UserID = u.ID 
                where u.RecordStatus != {RecordStatusEnum.Deleted.ToInt()} {where}";

            string orderField = "RecordStatus, CreateTime desc";

            var pageData = _db.GetSqlPageList(
                sql, orderField, req.PageNumber, req.PageSize, parms.ToArray());

            return Ok(new HttpResponseDto(HttpResponseCode.QuerySuccess, pageData));
        }

        [HttpPost]
        public async Task<IActionResult> Add(Sys_UserAddReq req)
        {
            await _db.Sys_User.CheckExistsAsync(it => it.UserName == req.UserName);

            var user = _mapper.Map<Sys_User>(req);

            using (var tran = await _db.Database.BeginTransactionAsync())
            {
                try
                {
                    // 批量添加时将自动跟踪禁用, 能提升效率
                    //_db.ChangeTracker.AutoDetectChangesEnabled = false;

                    await _db.AddAndSaveAsync(user);

                    var log = new Sys_UserLog
                    {
                        UserID = user.ID,
                        CreateTime = DateTime.Now,
                        Info = "EF Core 事务添加用户"
                    };

                    await _db.AddAndSaveAsync(log);

                    await tran.CommitAsync();
                }
                catch (Exception ex)
                {
                    await tran.RollbackAsync();

                    return Ok(new HttpResponseDto(HttpResponseCode.InsertFail, ex.Message));
                }
            }
            
            return Ok(new HttpResponseDto(HttpResponseCode.InsertSuccess, user.ID));
        }

        [HttpPost]
        public async Task<IActionResult> Update(Sys_UserUpdateReq req)
        {
            var user = await _db.Sys_User.GetFirstAsync(it => it.ID == req.ID);

            _mapper.Map(req, user);

            if (await _db.SaveAsync()) // 直接更新到数据库就行, 前面已经跟踪到实体被修改了
            {
                return Ok(new HttpResponseDto(HttpResponseCode.ModifySuccess));
            }

            return Ok(new HttpResponseDto(HttpResponseCode.ModifyFail));
        }

        [HttpPost]
        public async Task<IActionResult> Delete(PrimaryKeyReq req)
        {
            var user = await _db.Sys_User.GetFirstAsync(it => it.ID == req.ID);

            if (await _db.LogicalDeleteAndSaveAsync(user))
            {
                return Ok(new HttpResponseDto(HttpResponseCode.DeleteSuccess));
            }

            return Ok(new HttpResponseDto(HttpResponseCode.DeleteFail));
        }
    }
}
